﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DbUpgradeSqlite.General
{
    public class SqliteDal : IDisposable
    {
        private string _connstring = string.Empty;
        //Data Source=filename;Version=3;Password=myPassword;
        private SQLiteConnection _conn = null;
        private static object locker = new object();

        /// <summary>
        /// 创建sqlite控制类
        /// </summary>
        /// <param name="path"></param>
        public SqliteDal(string path)
        {
            _connstring = $"Data Source={path};Version=3;";
        }

        ~SqliteDal()
        {
            
        }

        private void OpenConn()
        {
            _conn = new SQLiteConnection(_connstring);

            if (_conn.State == ConnectionState.Closed)
            {
                _conn.Open();
            }
        }

        private void CloseConn()
        {
            if (_conn == null) { return; }
            try
            {
                if (_conn.State != ConnectionState.Closed)
                {
                    _conn.Close();
                }
            }
            finally
            {
                _conn.Dispose();
                _conn = null;
            }
        }

        public DataSet ExecDataSet(string sql)
        {
            lock (locker)
            {
                try
                {
                    OpenConn();
                    SQLiteCommand cmd = _conn.CreateCommand();
                    cmd.CommandText = sql;
                    DataSet ds = new DataSet();
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(ds);
                    da.Dispose();
                    return ds;
                }
                finally
                {
                    CloseConn();
                }
            }
        }

        public DataTable ExecDataTable(string sql)
        {
            lock (locker)
            {
                try
                {
                    OpenConn();
                    SQLiteCommand cmd = _conn.CreateCommand();
                    cmd.CommandText = sql;
                    DataSet ds = new DataSet();
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(ds);
                    da.Dispose();
                    DataTable dt = null;
                    if (ds.Tables.Count > 0)
                    {
                        dt = ds.Tables[0];
                    }
                    return dt;
                }
                finally
                {
                    CloseConn();
                } 
            }
        }

        public int ExecNoQuery(string sql)
        {
            lock (locker)
            {
                try
                {
                    OpenConn();
                    SQLiteCommand cmd = _conn.CreateCommand();
                    cmd.CommandText = sql;
                    int i = cmd.ExecuteNonQuery();
                    return i;
                }
                finally
                {
                    CloseConn();
                } 
            }
        }

        public object ExecScalar(string sql)
        {
            lock (locker)
            {
                try
                {
                    OpenConn();
                    SQLiteCommand cmd = _conn.CreateCommand();
                    cmd.CommandText = sql;
                    object o = cmd.ExecuteScalar();
                    return o;
                }
                finally
                {
                    CloseConn();
                } 
            }
        }

        public void Dispose()
        {
            CloseConn();
        }
    }
}
